import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

/**
 * A class that consists of the database operations to insert and update the Movie information.
 * @author mmuppa
 *
 */

public class MovieDB {
	private static String userName = "_445team5";
	private static String password = "Fiowgiv";
	private static String serverName = "cssgate.insttech.washington.edu";
	private static Connection conn;
	private List<Movie> list;
	private List<Review> reviewList;
	private List<User> userList;
	private List<Actor> actorList;

	/**
	 * Creates a sql connection to MySQL using the properties for
	 * userid, password and server information.
	 * @throws SQLException
	 */
	public static void createConnection() throws SQLException {
		Properties connectionProps = new Properties();
		connectionProps.put("user", userName);
		connectionProps.put("password", password);

		conn = DriverManager.getConnection("jdbc:" + "mysql" + "://"
				+ serverName + "/", connectionProps);

		System.out.println("Connected to database");
	}

	/**
	 * Returns a list of movie objects from the database.
	 * @return list of movies
	 * @throws SQLException
	 */
	public List<Movie> getMovies() throws SQLException {
		if (conn == null) {
			createConnection();
		}
		Statement stmt = null;
		String query = "select movieTitle, movieYear, movieDirector, movieSynopsis, movieRating "
				+ "from _445team5.Movie ";

		list = new ArrayList<Movie>();
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				String title = rs.getString("movieTitle");
				int year = rs.getInt("movieYear");
				String dir = rs.getString("movieDirector");
				String syn = rs.getString("movieSynopsis");
				int rating = rs.getInt("movieRating");
				Movie movie = new Movie(title, year, dir, syn, rating);
				list.add(movie);
			}
		} catch (SQLException e) {
			System.out.println(e);
		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
		return list;
	}

	/**
	 * Filters the movie list to find the given title. Returns a list with the
	 * movie objects that match the title provided.
	 * @param title
	 * @return list of movies that contain the title.
	 */
	public List<Movie> getMovies(String title) {
		List<Movie> filterList = new ArrayList<Movie>();
		try {
			list = getMovies();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		for (Movie movie : list) {
			if (movie.getTitle().toLowerCase().contains(title.toLowerCase())) {
				filterList.add(movie);
			}
		}
		return filterList;
	}

	/**
	 * Adds a new movie to the table.
	 * @param movie 
	 */
	public void addMovie(Movie movie) {
		String sql = "insert into _445team5.Movie value " + "(?, ?, ?, ?, 0); ";

		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setString(1, movie.getTitle());
			preparedStatement.setInt(2, movie.getYear());
			preparedStatement.setString(3, movie.getDirector());
			preparedStatement.setString(4, movie.getSynopsis());
			//preparedStatement.setInt(5, movie.getRating());
			System.out.println(preparedStatement.toString());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e);
			e.printStackTrace();
		} 
	}

	/**
	 * Modifies the movie information corresponding to the index in the list.
	 * @param row index of the element in the list
	 * @param columnName attribute to modify
	 * @param data value to supply
	 */
	public void updateMovie(int row, String columnName, Object data) {

		Movie movie = list.get(row);
		String title = movie.getTitle();
		int year = movie.getYear();
		String sql = "update _445team5.Movie set " + columnName + " = ?  where title= ? and year = ? ";
		System.out.println(sql);
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement(sql);
			if (data instanceof String)
				preparedStatement.setString(1, (String) data);
			else if (data instanceof Integer)
				preparedStatement.setInt(1, (Integer) data);
			preparedStatement.setString(2, title);
			preparedStatement.setInt(3, year);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e);
			e.printStackTrace();
		} 	
	}

	public List<User> getUser() throws SQLException {
		if (conn == null) {
			createConnection();
		}
		Statement stmt = null;
		String query = "select username, password, birthdate, userType "
				+ "from _445team5.User ";

		userList = new ArrayList<User>();
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				String username = rs.getString("username");
				String password = rs.getString("password");
				Date birthdate = rs.getDate("birthdate");
				String userType = rs.getString("userType");
				User u = new User(username, password, birthdate, userType);
				userList.add(u);
			}
		} catch (SQLException e) {
			System.out.println(e);
		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
		return userList;

	}


	/**
	 * Returns a list of review objects from the database.
	 * @return list of review
	 * @throws SQLException
	 */
	public List<Review> getReviews() throws SQLException {
		if (conn == null) {
			createConnection();
		}
		Statement stmt = null;
		String query = "select reviewUsername, reviewMovie, reviewMovieYear, comment, reviewRating "
				+ "from _445team5.Review ";

		reviewList = new ArrayList<Review>();
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				String username = rs.getString("reviewUsername");
				String title = rs.getString("reviewMovie");
				int year = rs.getInt("reviewMovieYear");
				String comm = rs.getString("comment");
				int rating = rs.getInt("reviewRating");
				Review review = new Review(username, title, year, comm, rating);
				reviewList.add(review);
			}
		} catch (SQLException e) {
			System.out.println(e);
		} finally {
			if (stmt != null) {
				stmt.close();
			}
		}
		for (Review r : reviewList)
		{
			System.out.println("Review id: " + r.getReviewID());
		}
		return reviewList;
	}
	/**
	 * Filters the review list to find the given movie title. Returns a list with the
	 * review objects that match the title provided.
	 * @param data
	 * @return list of review that about the title.
	 */
	public List<Review> getReviews(Object data) {
		List<Review> filterList = new ArrayList<Review>();
		if(reviewList != null)
		{
			for (Review r : reviewList) {
				if (r.getReviewMovieTitle().toLowerCase().contains(((String) data).toLowerCase())) {
					filterList.add(r);
				}
			}
		}
		else {
			try {
				reviewList = getReviews();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return filterList;
	}

	/**
	 * Adds a new review to the table.
	 * @param r 
	 */
	public void addReview(Review r) {
		String sql = "insert into _445team5.Review values " + "(?, ?, ?, ?, ?, ?); ";

		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setInt(1, r.getReviewID());
			preparedStatement.setString(2, r.getReviewUsername());
			preparedStatement.setString(3, r.getReviewMovieTitle());
			preparedStatement.setInt(4, r.getReviewMovieYear());
			preparedStatement.setString(5, r.getComment());
			preparedStatement.setInt(6, r.getReviewRating());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e);
			e.printStackTrace();
		} 

	}

	/**
	 * Modifies the review information.
	 * @param row index of the element in the list
	 * @param columnName attribute to modify
	 * @param data value to supply
	 */
	public void updateReview(int id, String newReview) {
		
		String sql = "update _445team5.Review set comment = ?  where reviewID= ? ";
		System.out.println(sql);
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setString(1, newReview);
			preparedStatement.setInt(2, id);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e);
			e.printStackTrace();
		} 	
		Review r = reviewList.get(id-1);
		r.setComment(newReview);
	}

	/**
	 * Deletes a review from the table.
	 * @param the review id 
	 */
	public void deleteReview(int id) {
		String sql = "delete from _445team5.Review where reviewID= ? ";
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e);
			e.printStackTrace();
		} 
		reviewList.remove(id-1);
	}
	
	public void addUser(User u) {
		String sql = "insert into _445team5.User values " + "(?, ?, ?, NULL); ";
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setString(1, u.getUsername());
			preparedStatement.setString(2, u.getPassword());

			java.sql.Date sqlDate = new java.sql.Date(u.getBirthdate().getTime());
			System.out.println("sqlDate:" + sqlDate);
			preparedStatement.setString(3, sqlDate.toString());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e);
			e.printStackTrace();
		}
	}

	public String getPassword(String u) {
		String query = "SELECT password FROM _445team5.User WHERE username = \"" + u + "\";";
		System.out.println(query);
		Statement stmt = null;
		String pw = "";
		try {
			System.out.println("Hello");
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			//System.out.println(rs.getString("password"));
			while (rs.next()) {
				pw = rs.getString("password");
				System.out.println(pw);
			}
		} catch (SQLException e) {
			System.out.println(e);
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		System.out.println(pw);
		return pw;
	}
	public boolean UserExists(String Username) {
		String query = "SELECT username FROM _445team5.User WHERE username = " + "\"" + Username + "\";";
		System.out.println(query);
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			System.out.println(e);
			return false;
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return false;
	}

	public boolean directorExist(String d) {
		String query = "SELECT directorUsername FROM _445team5.Director WHERE directorUsername = " + "\"" + d + "\";";
		System.out.println(query);
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			System.out.println(e);
			return false;
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return false;
	}

	public boolean actorExist(String a) {
		String query = "SELECT actorUsername FROM _445team5.Actor WHERE actorUsername = " + "\"" + a + "\";";
		System.out.println(query);
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			System.out.println(e);
			return false;
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return false;
	}

	public void addDirector(Director d, String u) {
		if(!directorExist(u)) {
			String sql = "insert into _445team5.Director values " + "(?, NULL); ";
			PreparedStatement preparedStatement = null;
			try {
				preparedStatement = conn.prepareStatement(sql);
				preparedStatement.setString(1, u);
				System.out.println(preparedStatement.toString());
				preparedStatement.executeUpdate();
				System.out.println("Added to Director Table");
			} catch (SQLException e) {
				System.out.println(e);
				e.printStackTrace();
			}
		}
		String query = "UPDATE _445team5.User SET UserType='D' WHERE username = " + "\"" + u + "\";";
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			int rs = stmt.executeUpdate(query);

			//			while (rs.next()) {
			//				System.out.println("Updated");
			//			}
		} catch (SQLException e) {
			System.out.println(e);
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	public void addActor(String u, Movie m) {
		if(!actorExist(u)) {
				String sql = "insert into _445team5.Actor values " + "(?, NULL); ";
				PreparedStatement preparedStatement = null;
				try {
					preparedStatement = conn.prepareStatement(sql);
					preparedStatement.setString(1, u);
					System.out.println(preparedStatement.toString());
					preparedStatement.executeUpdate();
					System.out.println("Added to Actor Table");
				} catch (SQLException e) {
					System.out.println(e);
					e.printStackTrace();
				}
		}
		String query = "UPDATE _445team5.User SET UserType='A' WHERE username = " + "\"" + u + "\";";
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			int rs = stmt.executeUpdate(query);

			//			while (rs.next()) {
			//				System.out.println("Updated");
			//			}
		} catch (SQLException e) {
			System.out.println(e);
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		String sql1 = "insert into _445team5.StarsIn values " + "(?, ?, ?); ";
		PreparedStatement preparedStatement1 = null;
		try {
			preparedStatement1 = conn.prepareStatement(sql1);
			preparedStatement1.setString(1, m.getTitle());
			preparedStatement1.setInt(2, m.getYear());
			preparedStatement1.setString(3, u);
			System.out.println(preparedStatement1.toString());
			preparedStatement1.executeUpdate();
			System.out.println("Added to StarsIn Table");
		} catch (SQLException e) {
			System.out.println(e);
			e.printStackTrace();
		}
		String sql = "insert into _445team5.PermissionsAdded values " + "(0, ?, ?, ?); ";
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setString(1, m.getTitle());
			preparedStatement.setInt(2, m.getYear());
			preparedStatement.setString(3, u);
			System.out.println(preparedStatement.toString());
			preparedStatement.executeUpdate();
			System.out.println("Added to Actor Table");
		} catch (SQLException e) {
			System.out.println(e);
			e.printStackTrace();
		}
	}
	public String getMovieDirector(Movie m) {
		String query = "SELECT movieDirector FROM _445team5.Movie WHERE movieTitle = \"" + m.getTitle() + "\" AND movieYear = " + m.getYear() + ";";
		System.out.println(query);
		Statement stmt = null;
		String director = "";
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			//System.out.println(rs.getString("password"));
			while (rs.next()) {
				director = rs.getString("movieDirector");
				System.out.println(director);
			}
		} catch (SQLException e) {
			System.out.println(e);
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return director;
	}
}
